Summing and Grouping
This example demonstrates how to use the Summing and Grouping capabilities in the Argos DataBlock Designer. This capability allows you to visually create SQL HAVING and GROUP BY statements.
This simple example will create a Dashboard in which sales results of employees will be presented. Results will be grouped by employee; thus the SQL GROUP BY statement will be created. The results will show only sales totals below a specified amount, with an SQL HAVING statement created. Also, the SQL aggregate SUM function will be visually generated to create the sales totals.
The Employees, Orders, Order_Details, and Products tables within the sample database will be utilized. These are the same tables that were used in Example 1.
New SQL Statements in this example: GROUP BY, HAVING, and SQL AGGREGATE functions.
Creating the Form
The input required from the user for this report will be a date range, and a sales total threshold that will be used to filter the query.
Create a form as shown below. The variable names for the three input selection fields are StartDate, EndDate, and SalesTotal. The SalesTotal variable will be used to specify a sales total that is used to display names of sales persons whose total are less than this amount.
The query for the multi-column list box containing the query results are shown in the figure below. Only two fields will be displayed in the Dashboard, the employee ID and a calculated field (total_sales) containing the sales total (quantity times unit price).
The WHERE clause below is used to find records within the date range specified by the person executing the report.
GROUP BY and SUM fields
To sum the sales records and group them by employee,
- Click the “Summing” icon which displays an additional row titled “Summing” as shown below.
- Under the Employees column, select <Group By>.
- Under the <calculated>, select Sum for the calculated field since this data is to be summed. These selections are used to create the GROUP BY statement.
HAVING Tab
The HAVING tab in the figure below specifies a condition where sales total is less than the amount entered. The SalesTotal variable contains the value entered by the user executing the report with that value compared to the calculated field above it. If the SalesTotal is less than or equal to the input selection, that employee will be included in the report. This data is used to create the SQL HAVING statement.
The final SQL:
Note the existence of the GROUP BY and HAVING statements.
Creating complex WHERE and HAVING statements:
Note the box at the left portion of the window containing the SELECT,WHERE, HAVING, ORDER BY tabs. By clicking <root> then clicking the plus sign, you can create nested WHERE or HAVING clauses, each enclosed in parentheses and containing its own set of conditionals.
The tree structure above would create SQL structured as follows:
WHERE [Join conditions]
AND [conditions in #1 ]
AND [conditions in #2]
AND [conditions in #3]
AND [conditions in #4]
Results
Executing the Dashboard report produces the following which lists sales employees with sales less than $1,000,000 between 1/3/2005 and 4/5/2010.
Return to the Charting with multiple series example